import numpy as np
import pymysql
import pandas as pd
import pymysql.cursors

class MysqlUtils(object):
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            passwd='root',
            db='scenic',
            port=3306,
            charset='utf8'
        )
    
    def get_scenic_data(self):
        """获取景区数据并进行统计分析"""
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        
        sql = """
        SELECT t.tourist_agency_name, rel.id_no, LEFT(rel.id_no, 2) as province_code,
        DAYOFWEEK(gate.create_time) as day_of_week,
        CAST(SUBSTRING(rel.id_no, 7, 4) AS UNSIGNED) as birth_year
        FROM ticket_order_user_rel rel
        LEFT JOIN ticket_order t ON t.id = rel.order_id
        LEFT JOIN order_user_gate_rel gate ON gate.ticket_rel_id = rel.id
        WHERE t.pay_time != '' AND t.tourist_agency_name != ''
        """
        
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        
        # 数据清洗
        # 标记非周末 (1=周日, 2=周一,...,7=周六)
        df['non_weekend'] = df['day_of_week'].apply(lambda x: 1 if x not in [1, 7] else 0)
        
        # 新增有效标记列
        df['valid_id'] = df['id_no'].apply(lambda x: 1 if x and str(x).strip() != '' else 0)
        
        # 判断是否外省游客 (假设44是本地省份代码)
        df['out_province_ratio'] = df.apply(
            lambda x: 1 if (x['valid_id'] and x['province_code'] != '44') else 0 if x['valid_id'] else np.nan,
            axis=1
        )
        
        # 判断是否老年游客 (60岁以上)
        df['elderly_ratio'] = df.apply(
            lambda x: 1 if (x['valid_id'] and 2025 - x['birth_year'] >= 60) else 0 if x['valid_id'] else np.nan,
            axis=1
        )
        
        # 分组聚合计算占比
        result = df.groupby(['tourist_agency_name']).agg(
            total_visitors=('id_no', 'count'),  # 总游客数
            valid_visitors=('valid_id', 'sum'),  # 有效身份证游客数
            out_province=('out_province_ratio', 'sum'),  # 外省人数（有效id_no）
            elderly=('elderly_ratio', 'sum'),  # 老年人人数
            non_weekend_ratio=('non_weekend', 'mean')  # 非周末占比
        ).reset_index()
        
        # 计算各种比例
        result['valid_ratio'] = result['valid_visitors'] / result['total_visitors']
        result['out_province_ratio'] = result['out_province'] / result['valid_visitors']
        result['elderly_ratio'] = result['elderly'] / result['valid_visitors']
        
        # 关闭数据库连接
        cursor.close()
        
        return result

if __name__ == '__main__':
    mu = MysqlUtils()
    result = mu.get_scenic_data()
    print(result.head())